/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import DataAccess.DBUtil;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;


/**
 *
 * @author Toshiba Satellite
 */
public class Company {


    public static ArrayList<Entity.Company> getAll()
    {
        String sql = "{call pcGetAllCompany}";
        ArrayList<Entity.Company> arrCom = new ArrayList<Entity.Company>();
        
        try {
            CallableStatement cs = DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsCom = cs.executeQuery();
            while(rsCom.next())
            {
                Entity.Company com = new Entity.Company();

                com.setCompanyID(rsCom.getInt("CompanyID"));
                com.setName(rsCom.getString("Name"));
                com.setAddress(rsCom.getString("Address"));
                com.setPhoneNumber(rsCom.getString("PhoneNumber"));
                com.setIntroduction(rsCom.getString("Introduction"));
                com.setWebsite(rsCom.getString("Website"));
                com.setFax(rsCom.getString("fax"));

                arrCom.add(com);

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
              return arrCom;

    }

    public static ArrayList<Entity.Company> getTop()
    {
        String sql = "{call pcGetTopCompany}";
        ArrayList<Entity.Company> arrCom = new ArrayList<Entity.Company>();

        try {
            CallableStatement cs = new DBUtil().getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsCom = cs.executeQuery();
            while(rsCom.next())
            {
                Entity.Company com = new Entity.Company();

                com.setCompanyID(rsCom.getInt("CompanyID"));
                com.setName(rsCom.getString("Name"));
                com.setAddress(rsCom.getString("Address"));
                com.setPhoneNumber(rsCom.getString("PhoneNumber"));
           //     com.setIntroduction(rsCom.getString("Introduction"));
                com.setWebsite(rsCom.getString("Website"));
                com.setFax(rsCom.getString("fax"));

                arrCom.add(com);

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
            return arrCom;
    }
    
    public static boolean insert(Entity.Company com)
    {
        String sql = "{call pcInsertCompany(?,?,?,?,?,?)}";
        
        try {
            
            CallableStatement cs = new DBUtil().getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            cs.setString("Name", com.getName());
            cs.setString("Address", com.getAddress());
            cs.setString("PhoneNumber", com.getPhoneNumber());
            cs.setString("Introduction", com.getIntroduction());
            cs.setString("Website", com.getWebsite());
            cs.setString("fax", com.getFax());
            
            cs.executeUpdate();
            
            return true;
        } catch (Exception e) {
            
            return false;
        }
        
    }
    
    public static boolean update(Entity.Company com)
    {
        String sql = "{call pcUpdateCompany(?,?,?,?,?,?,?)}";
        try {
            CallableStatement cs = new DBUtil().getConnection(DBUtil.getInfoDB()).prepareCall(sql);
            
            cs.setInt("CompanyID", com.getCompanyID());
            cs.setString("Name", com.getName());
            cs.setString("Address", com.getAddress());
            cs.setString("PhoneNumber", com.getPhoneNumber());
            cs.setString("Introduction", com.getIntroduction());
            cs.setString("Website", com.getWebsite());
            cs.setString("fax", com.getFax());
            
            cs.executeUpdate();
            
            return true;
        } catch (Exception e) {
            return false;
        }
        
        
        
    }

    public static boolean delete(Entity.Company com)
    {
        String sql= "{call pcDeleteCompany(?)}";
        try {
            CallableStatement cs =  DBUtil.getConnection(DBUtil.getInfoDB()).prepareCall(sql)   ;

            cs.setInt(1, com.getCompanyID());
            cs.executeUpdate();

            return true;
        } catch (Exception e) {
            return false;
        }
    }




    public static ArrayList<Entity.Company> getAllClientName()
    {
        String sql = "{call pcGetAllName}";
        ArrayList<Entity.Company> arrCom = new ArrayList<Entity.Company>();

        try {
            CallableStatement cs = new DBUtil().getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsCom = cs.executeQuery();

            while(rsCom.next())
            {
                Entity.Company com = new Entity.Company();

                com.setCompanyID(rsCom.getInt("CompanyID"));
                com.setName(rsCom.getString("Name"));
                com.setAddress(rsCom.getString("Address"));
                com.setPhoneNumber(rsCom.getString("PhoneNumber"));
                com.setIntroduction(rsCom.getString("Introduction"));
                com.setWebsite(rsCom.getString("Website"));
                com.setFax(rsCom.getString("fax"));

                arrCom.add(com);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
        return arrCom;
    }

    public static int getCompanyID(String name)
    {
        String sql = "select CompanyID from Company where Name='"+name+"'";
        int ClientID = 0;

        try {
            Statement stmt = new DBUtil().getConnection(DBUtil.getInfoDB()).createStatement();
            ResultSet rsCom = stmt.executeQuery(sql);

            while(rsCom.next())
            {
                ClientID = rsCom.getInt("CompanyID");
            }

        } catch (Exception e) {
            e.printStackTrace();

        }
        return ClientID;
    }

    public static int getTop1CompanyID()
    {
        String sql = "{call pcGetTop1CompanyID}";
        int CompanyID = 0;

        try {
            CallableStatement cs = new DBUtil().getConnection(DBUtil.getInfoDB()).prepareCall(sql);

            ResultSet rsCom = cs.executeQuery();

            while(rsCom.next())
                CompanyID = rsCom.getInt("CompanyID");


        } catch (Exception e) {
            e.printStackTrace();
        }

        return CompanyID;
    }


    public static String getCompanyName(int ID)
    {
        String sql = "select Name from Company where CompanyID="+ID;
        String Name = null;

        try {
            Statement stmt = new DBUtil().getConnection(DBUtil.getInfoDB()).createStatement();
            ResultSet rsCom = stmt.executeQuery(sql);

            while(rsCom.next())
            Name = rsCom.getString("Name");

        } catch (Exception e) {
            e.printStackTrace();
        }

        return Name;

    }




}


